﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace Adatkezelo.Classes {
    public class SQLDataManipulation {
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
        public void OpenConn() {
            if (con.State != ConnectionState.Open)
                con.Open();
        }
        public void CloseConn() {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
        public SqlDataReader getDataByID(int ID, string from, string key) {
            string sqlQuerry = "SELECT *" +
                                    "   FROM " + from +
                                    "   WHERE " + key + " = '" + ID + "'";
            return getDatabyCommand(sqlQuerry);
        }
        /// <summary>
        /// After this command if the sql data reader not needed call CloseConn() !
        /// </summary>
        /// <param name="sqlQuerry"></param>
        /// <returns></returns>
        public SqlDataReader getDatabyCommand(string sqlQuerry) {
            SqlCommand cmd;
            SqlDataReader rdr;
            try {
                OpenConn();                        //sql kapcsolat megnyitása
                cmd = new SqlCommand(sqlQuerry);      //sql parancs létrehozás
                cmd.Connection = con;                   //megmondjuk min hajtsa végre 
                rdr = cmd.ExecuteReader();              //végrehajt
            } catch (Exception exp) {
                return null;
            }
            return rdr;
        }

        //asszinkron modon hajt vegre 1sql parancsot, adatmodosito utasitasok csak igy hajthatok vegre pl insert vagy update
        /// <summary>
        /// Executes the given indert or update command.
        /// </summary>
        /// <param name="Command">insert or update sql querry needed</param>
        /// <returns></returns>
        public int setDatabyCommand(string Command) {
            con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True;Asynchronous Processing=true");
            //connectionbuild.AsynchronousProcessing = true;
            //SqlConnection connection = new SqlConnection(connectionbuild.ConnectionString);
            SqlCommand sqlparancs = new SqlCommand(Command);
            sqlparancs.Connection = con;
            OpenConn();
            IAsyncResult result = sqlparancs.BeginExecuteNonQuery();
            int a = sqlparancs.EndExecuteNonQuery(result);
            CloseConn();
            return a;
        }
        /// <summary>
        /// It uploads the file into the database
        /// </summary>
        /// <param name="ctrFile"></param>
        /// <returns></returns>
        public Guid UploadFile(System.Web.UI.WebControls.FileUpload ctrFile) {
            // exit if file-upload has no file
            if (!ctrFile.HasFile) return Guid.Empty;

            Guid fileId = Guid.NewGuid();
            // create insert query
            using (SqlCommand command = new SqlCommand()) {
                command.Connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True;Asynchronous Processing=true");
                command.CommandText = @"INSERT INTO Tananyag (Tan_ID, FileName, Type, FileSize, FileContent) 
						VALUES(@FileId, @FileName, @FileType, @FileSize, @FileContent)";
                command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId;
                command.Parameters.Add("@FileName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(ctrFile.PostedFile.FileName);
                command.Parameters.Add("@FileType", SqlDbType.NVarChar, 100).Value = ctrFile.PostedFile.ContentType;
                command.Parameters.Add("@FileSize", SqlDbType.Int).Value = ctrFile.PostedFile.ContentLength;
                // filecontent, convert from stream to byte array
                byte[] fileContent = new byte[ctrFile.PostedFile.ContentLength];
                ctrFile.PostedFile.InputStream.Read(fileContent, 0, ctrFile.PostedFile.ContentLength);
                command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent;
                command.Connection.Open();
                command.ExecuteNonQuery();
                return fileId;
            }
        }

        public int getLatestIDForTable(string TableName, string key) {
            OpenConn();
            SqlDataReader rdr = getDatabyCommand("SELECT MAX(" + key + ") FROM " + TableName);
            rdr.Read();
            int tmp = (int)rdr[0];
            CloseConn();
            return tmp;
        }
    }
}